package com.cskaoyan.mall.dao;

import com.cskaoyan.mall.model.goods.*;
import com.cskaoyan.mall.model.message.Reply;
import com.cskaoyan.mall.model.message.ReplyMsgVO;
import com.cskaoyan.mall.model.orders.OrdersByStateVOInnerGoods;
import com.cskaoyan.mall.model.user.User;
import com.cskaoyan.mall.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @author: jyf2000jyf
 */
public class GoodsDaoImpl implements GoodsDao {
    @Override
    public List<Type> getType() {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<Type> typeList = null;
        try {
            typeList = queryRunner.query("select id,name from type", new BeanListHandler<>(Type.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return typeList;
    }

    @Override
    public List<GoodsByTypeVO> getGoodsByType(int typeId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<GoodsByTypeVO> goodsByTypeVOList = null;
        try {
            goodsByTypeVOList = queryRunner.query("select id,image as img,name,price,typeId,stockNum from goods where typeId = ?", new BeanListHandler<>(GoodsByTypeVO.class), typeId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsByTypeVOList;
    }

    @Override
    public boolean addGoods(Goods goods) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("insert into goods values (null,?,?,?,?,?,?)", goods.getName(), goods.getTypeId(), goods.getImage(), goods.getDescription(), goods.getPrice(), goods.getStockNum());
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    @Override
    public int selectMaxGoodsId() {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        Integer query = null;
        try {
            query = queryRunner.query("select max(id) from goods", new ScalarHandler<>());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    @Override
    public boolean addSpecList(List<Spec> specList) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        for (Spec spec : specList) {
            try {
                queryRunner.update("insert into spec values (null,?,?,?,?)", spec.getSpecName(), spec.getStockNum(), spec.getUnitPrice(), spec.getGoodsId());
            } catch (SQLException e) {
                return false;
            }
        }
        return true;
    }

    @Override
    public boolean addType(Type type) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("insert into type values (null,?)", type.getName());
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    @Override
    public boolean deleteGoodsById(int id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("delete from goods where id = ?", id);
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    @Override
    public boolean deleteType(int typeId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("delete from type where id = ?", typeId);
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    @Override
    public boolean deleteGoodsByTypeId(int typeId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("delete from goods where typeId = ?", typeId);
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    public boolean deleteSpecByTypeId(int typeId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<Integer> goodsIdList = null;
        try {
            queryRunner.update("delete from spec where goodsId in (select id from goods where typeId = ?)", typeId);
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    @Override
    public List<Spec> getSpecList(int goodsId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<Spec> specList = null;
        try {
            specList = queryRunner.query("select id,specName,stockNum,unitPrice from spec where goodsId = ?", new BeanListHandler<>(Spec.class), goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return specList;
    }

    @Override
    public Goods getGoodsVOByGoodsId(int goodsId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        Goods goods = null;
        try {
            goods = queryRunner.query("select id,image,name,description,typeId from goods where id = ?", new BeanHandler<>(Goods.class), goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

    @Override
    public boolean updateGoods(Goods goods, int goodsId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("update goods set name = ?,typeId = ?,image = ?,description = ? where id = ?", goods.getName(), goods.getTypeId(), goods.getImage(), goods.getDescription(), goodsId);
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    @Override
    public boolean deleteSpecByGoodsId(int goodsId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("delete from spec where goodsId = ?", goodsId);
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    @Override
    public void updateStockNumAndPrice(int goodsId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("update goods set price = (select min(unitPrice) from spec where goodsId = ?),stockNum = (select max(stockNum) from spec where goodsId = ?) where id = ?", goodsId, goodsId, goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public boolean addSpec(Spec spec) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            Long query = queryRunner.query("select count(*) from spec where specName = ? and goodsId = ?", new ScalarHandler<>(), spec.getSpecName(), spec.getGoodsId());
            if (query.intValue() == 1) {
                return false;
            }
            queryRunner.update("insert into spec values (null,?,?,?,?)", spec.getSpecName(), spec.getStockNum(), spec.getUnitPrice(), spec.getGoodsId());
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    @Override
    public boolean deleteSpec(Spec spec) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("delete from spec where specName = ? and goodsId = ?", spec.getSpecName(), spec.getGoodsId());
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    @Override
    public List<ReplyMsgVO> getNoReplyMsg() {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<ReplyMsgVO> replyMsgVOList = null;
        try {
            replyMsgVOList = queryRunner.query("select id,userId,username,goodsId,goodsName,content,state,createtime from message where state = 0", new BeanListHandler<>(ReplyMsgVO.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return replyMsgVOList;
    }

    @Override
    public List<ReplyMsgVO> getRepliedMsg() {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<ReplyMsgVO> replyMsgVOList = null;
        try {
            replyMsgVOList = queryRunner.query("select * from message where state = 1", new BeanListHandler<>(ReplyMsgVO.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return replyMsgVOList;
    }

    @Override
    public boolean reply(Reply replyContent) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("update message set state = 1,replytime = curtime(),replyContent = ? where id = ?", replyContent.getContent(), replyContent.getId());
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    @Override
    public List<GoodsByTypeVO> getGoodsOrderByDesc() {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<GoodsByTypeVO> goodsByTypeVOList = null;
        try {
            goodsByTypeVOList = queryRunner.query("select id,image as img,name,price,typeId,stockNum from goods order by id desc", new BeanListHandler<>(GoodsByTypeVO.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsByTypeVOList;
    }

    @Override
    public List<GoodsByNameVO> getGoodsByName(String goodsName) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<GoodsByNameVO> goodsByNameVOList = null;
        try {
            goodsByNameVOList = queryRunner.query("select id,image as img,name,price,typeId from goods where name like ?", new BeanListHandler<>(GoodsByNameVO.class), "%" + goodsName + "%");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsByNameVOList;
    }

    @Override
    public List<GoodsMsgVO> getGoodsMsg(Integer id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<GoodsMsgVO> goodsMsgVOList = null;
        try {
            goodsMsgVOList = queryRunner.query("select id,content,username as asker,createtime as time,replytime,replyContent from message where id = ?", new BeanListHandler<>(GoodsMsgVO.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsMsgVOList;
    }

    @Override
    public List<GoodsCommentVOInnerComment> getGoodsComment(Integer goodsId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        List<GoodsCommentVOInnerComment> goodsCommentVOInnerCommentList = null;
        try {
            goodsCommentVOInnerCommentList = queryRunner.query("select id,userId,specName,score,comment,createTime as time,username from comment where goodsId = ?", new BeanListHandler<>(GoodsCommentVOInnerComment.class), goodsId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsCommentVOInnerCommentList;
    }

    @Override
    public String getGoodsNameByID(int id) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String goodsName = null;
        try {
            Goods goods = queryRunner.query("select name from goods where id = ?", new BeanHandler<>(Goods.class), id);
            goodsName = goods.getName();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsName;
    }

    @Override
    public OrdersByStateVOInnerGoods getGoodsBySpec(Integer goodsDetailId) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        OrdersByStateVOInnerGoods goods = null;
        try {
            goods = queryRunner.query("select goods.id,image as img,name,specName,unitPrice from goods,spec where goods.id = spec.goodsId and spec.id = ?",new BeanHandler<>(OrdersByStateVOInnerGoods.class),goodsDetailId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

    @Override
    public boolean askGoodsMsg(AskGoodsMsg askGoodsMsg) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            queryRunner.update("insert into message values(null,?,?,?,?,?,null,0,curtime(),null)",
                    askGoodsMsg.getUserId(),askGoodsMsg.getUsername(),askGoodsMsg.getGoodsId(),askGoodsMsg.getGoodsName(),askGoodsMsg.getContent());
        } catch (SQLException e) {
            return false;
        }
        return true;
    }
}
